const mongoose = require('mongoose');

// 成绩模型定义
const gradeSchema = new mongoose.Schema({
  studentId: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Student',
    required: true,
    index: true
  },
  studentName: {
    type: String,
    trim: true
  },
  subject: {
    type: String,
    required: true,
    trim: true
  },
  score: {
    type: Number,
    required: true,
    min: 0,
    max: 150
  },
  term: {
    type: String,
    required: true,
    trim: true,
    index: true
  },
  examType: {
    type: String,
    required: true,
    trim: true
  },
  rank: {
    type: Number
  },
  remark: {
    type: String,
    trim: true
  },
  academy: {
    type: String,
    trim: true
  },
  className: {
    type: String,
    trim: true
  },
  createdAt: {
    type: Date,
    default: Date.now
  },
  updatedAt: {
    type: Date,
    default: Date.now
  }
});

// 索引以提高查询性能
gradeSchema.index({ studentId: 1, term: 1, subject: 1, examType: 1 });

// 更新updatedAt字段的中间件
gradeSchema.pre('save', function(next) {
  this.updatedAt = Date.now();
  next();
});

// 静态方法：获取学生的平均成绩
gradeSchema.statics.getStudentAverageScore = async function(studentId) {
  const result = await this.aggregate([
    { $match: { studentId } },
    { $group: { _id: null, averageScore: { $avg: '$score' } } }
  ]);
  return result.length > 0 ? result[0].averageScore : 0;
};

// 静态方法：获取科目成绩统计
gradeSchema.statics.getSubjectStats = async function(subject, term) {
  const query = { subject };
  if (term) {
    query.term = term;
  }
  
  const result = await this.aggregate([
    { $match: query },
    {
      $group: {
        _id: null,
        averageScore: { $avg: '$score' },
        highestScore: { $max: '$score' },
        lowestScore: { $min: '$score' },
        count: { $sum: 1 },
        passedCount: { $sum: { $cond: [{ $gte: ['$score', 60] }, 1, 0] } }
      }
    }
  ]);
  
  return result.length > 0 ? result[0] : null;
};

const Grade = mongoose.model('Grade', gradeSchema);

module.exports = Grade;